#-*- coding: utf-8 -*
#@author 小测试
#@create 2020-03-12 23:19
"""
操作xls表格
"""
import os

import xlrd
from xlutils.copy import copy

from ApiDemo.util import get_log
from ApiDemo.util.tools.open_ini import Config


class OpenExcel:
    def __init__(self, file_path=None):
        if file_path == None:
            self.file_path = "apiCase.xls"
        else:
            self.file_path = file_path
        self.log=get_log.MyLog()
        self.config=Config()
        self.excel = self.get_excel()#打开文件
        self.data = self.get_sheets()#获取第几个标签页

    def get_excel(self):
        '''
        获取excel
        '''
        excel = xlrd.open_workbook(self.file_path,formatting_info=True)#formatting_info=True,保留原格式
        self.log.info("读取测试用例--->".format(self.file_path))
        return excel

    def get_sheets(self):
        '''
        获取sheets的内容
        '''
        tables = self.excel.sheets()

        return tables

    def get_names(self):
        names=self.excel.sheet_names()
        return names

    def get_lines(self):
        '''
        获取excel行数
        '''
        lines = self.data.nrows
        return lines

    def get_cell(self, row, cell):
        '''
        获取单元格内容
        '''
        data = self.data.cell(row, cell).value
        return data

    # def write_value(self, row, value):
    #     """
    #     保存数据到文件中
    #     row: 行
    #     value: 数据
    #     """
    #     read_value = self.excel
    #     write_data = copy(read_value)
    #     write_save = write_data.get_sheet(self.page)
    #     write_save.write(row, ExcelSign["实际结果"], value)
    #     write_data.save(self.file_path)
    def write_value(self,row,value):
        '''
        写入excel数据
        row,col,value
        '''
        read_data = self.get_excel()
        write_data = copy(read_data)
        sheet_data = write_data.get_sheet(0)
        sheet_data.write(row,self.config.case_name)
        write_data.save(self.file_path)

    def get_all_json(self):
        """
        excle的数据取出为json格式
        标签页为一级key,对应数据为标签页内的json
        第一列为二级key,对应数据为字段名:对应值
        字段名为三级key,字段名:值
        """
        excel_data={}
        names=self.get_names()
        for index in range(len(names)):
            sheet=self.get_sheets()[index]
            sheet_data={}
            print(names[index],sheet.nrows)
            for row in range(1,sheet.nrows):
                _id = int(sheet.cell(row,0).value)
                sheet_data[_id] = {}
                for  cell in range(1,sheet.row_len(0)):
                    key = sheet.cell(0,cell).value
                    value = sheet.cell(row,cell).value
                    sheet_data[_id][key]=value
            excel_data[sheet.name] = sheet_data
        self.log.info("提取测试用例为Json格式")
        return excel_data
if __name__ == '__main__':
    open=OpenExcel("../../cases/user/use_api_case.xls")
    data=open.get_all_json()
    for key,value in data.items():
        print(key,value)
    # for i in data:
    #     print(i)
    # print(open.sheet_to_json())
